package com.fr.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import org.apache.commons.dbutils.ResultSetHandler;
import com.fr.model.Coach;
import com.fr.model.Service;
import com.fr.model.ServiceType;

public class ServiceDao extends BaseDao{
	private static String selectSql = "select S.service_id sservice_id, S.subject ssubject,S.photo sphoto,"
			+ " S.coverpath scoverpath,S.price sprice, S.message smessage,"
			+ " S.recommend srecommend, S.detail sdetail, S.coach_id scoach_id,"
			+ " S.servicetype_id sservicetype_id,"
			+ " ST.id stid,ST.icon sticon,ST.title sttitle,"
			+ " C.coach_id ccoach_id, C.company ccompany, C.photo cphoto,"
			+ " C.avatar cavatar,C.nickname cnickname, C.price cprice,"
			+ " C.message cmessage, C.message cmessage, C.Lat clat,C.lon clon"
			+ " from service S"
			+ " left join servicetype ST on S.servicetype_id = ST.id"
			+ " left join coach C on S.coach_Id = C.coach_Id";
	public List<Service> getServicesByTypeId(int servicetype_id){
		try {
			String sql = selectSql + 
					" where servicetype_id = ?";
			return queryRunner.query(sql, new ResultSetHandler<List<Service>>() {
				@Override
				public List<Service> handle(ResultSet rs) throws SQLException {
					List<Service> services = new LinkedList<Service>();
					while(rs.next()) {
						services.add(
								new Service(
								rs.getInt("sservice_id"),
								rs.getString("ssubject"),
								rs.getString("sphoto"),
								rs.getString("scoverpath"),
								rs.getInt("sprice"),
								rs.getString("smessage"),
								rs.getString("srecommend"),
								rs.getString("sdetail"),
								new Coach(
										rs.getInt("ccoach_id"),
										rs.getString("ccompany"),
										rs.getString("cphoto"),
										rs.getString("cavatar"),
										rs.getString("cnickname"),
										rs.getInt("cprice"),
										rs.getString("cmessage"),
										rs.getDouble("clat"),
										rs.getDouble("clon")
										),
								new ServiceType(
										rs.getInt("stid"),
										rs.getString("sticon"),
										rs.getString("sttitle")
										)
								)
						);
					}
					return services;
				}
			},servicetype_id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}